/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.dbdir.core.datastore.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import org.dbdir.core.Logger;
import org.dbdir.core.helper.ACL;
import org.dbdir.core.helper.Admin;
import org.dbdir.core.helper.Database;
import org.dbdir.core.helper.User;

/**
 *
 * @author thotheolh
 */
public class SQLManager {

    public Connection conn = null;
    private Logger logger = new Logger();

    public SQLManager(String connPath, String username, String password) {
        conn = new DBManager(connPath, username, password).getConnection();
    }

    public SQLManager(Connection connection) {
        conn = connection;
    }

    public boolean addUser(User user) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("insert into User values(?,?)");
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public boolean updateUser(String username, String password) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("update User set password = ? where username = ?");
            pstmt.setString(1, password);
            pstmt.setString(2, username);
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public boolean deleteUser(String username) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("delete from User where username = ?");
            pstmt.setString(1, username);
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public User getUserByName(String username) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("select * from User where username = ?");
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return new User(rs.getString("username"), rs.getString("password"));
            } else {
                return null;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return null;
        }
    }

    public ArrayList getUserDBList(String username) {
        ArrayList dbList = new ArrayList();
        try {
            PreparedStatement pstmt = conn.prepareStatement("select dbAlias from ACL where username = ?");
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                dbList.add(rs.getString("dbAlias"));
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
        }
        return dbList;
    }

    public ArrayList listUsername() {
        ArrayList aList = new ArrayList();
        try {
            PreparedStatement pstmt = conn.prepareStatement("select username from User");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                aList.add(rs.getString("username"));
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
        }
        return aList;
    }

    public boolean addDatabaseInfo(Database db) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("insert into DatabaseInfo values(?,?,?,?,?)");
            pstmt.setString(1, db.getDbAlias());
            pstmt.setString(2, db.getDbPath());
            pstmt.setString(3, db.getDbUsername());
            pstmt.setString(4, db.getDbPassword());
            Date date = new Date();
            Timestamp timestamp = new Timestamp(date.getTime());
            pstmt.setTimestamp(5, timestamp);
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public boolean updateDatabaseInfo(Database db) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("update DatabaseInfo set dbusername=?, dbpassword=?, dbpath=? where dbalias=?");
            pstmt.setString(1, db.getDbUsername());
            pstmt.setString(2, db.getDbPassword());
            pstmt.setString(3, db.getDbPath());
            pstmt.setString(4, db.getDbAlias());
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public boolean deleteDatabaseInfo(String dbAlias) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("delete from DatabaseInfo where dbAlias = ?");
            pstmt.setString(1, dbAlias);
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public Database getDatabaseInfoByName(String dbAlias) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("select * from DatabaseInfo where dbAlias = ?");
            pstmt.setString(1, dbAlias);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                Database db = new Database(rs.getString("dbAlias"), rs.getString("dbPath"), rs.getString("dbUsername"), rs.getString("dbPassword"), rs.getTimestamp("datetimeRegistered"));
                return db;
            } else {
                return null;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return null;
        }
    }

    public ArrayList listDBAlias() {
        ArrayList aList = new ArrayList();
        try {
            PreparedStatement pstmt = conn.prepareStatement("select dbAlias from DatabaseInfo");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                aList.add(rs.getString("dbAlias"));
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
        }
        return aList;
    }

    public boolean addACL(ACL access) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("insert into ACL values(?,?,?,?,?,?,?,?,?)");
            pstmt.setString(1, access.getUsername());
            pstmt.setString(2, access.getDbAlias());
            pstmt.setBoolean(3, access.isShowDbPath());
            pstmt.setBoolean(4, access.isShowDbUsername());
            pstmt.setBoolean(5, access.isShowDbPassword());
            pstmt.setBoolean(6, access.isShowDateTimeReg());
            pstmt.setBoolean(7, access.isEditDbPath());
            pstmt.setBoolean(8, access.isEditDbUsername());
            pstmt.setBoolean(9, access.isEditDbPassword());

            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public boolean updateACL(ACL access) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("update ACL set showDbPath=?, showDbUsername=?, showDbPassword=?, showDateTimeReg=?, editDbPath=?, editDbUsername=?, editDbPassword=? where username=? and dbAlias=?");
            pstmt.setBoolean(1, access.isShowDbPath());
            pstmt.setBoolean(2, access.isShowDbUsername());
            pstmt.setBoolean(3, access.isShowDbPassword());
            pstmt.setBoolean(4, access.isShowDateTimeReg());
            pstmt.setBoolean(5, access.isEditDbPath());
            pstmt.setBoolean(6, access.isEditDbUsername());
            pstmt.setBoolean(7, access.isEditDbPassword());
            pstmt.setString(8, access.getUsername());
            pstmt.setString(9, access.getDbAlias());
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public boolean deleteACL(String username, String dbAlias) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("delete from ACL where username = ? and dbAlias = ?");
            pstmt.setString(1, username);
            pstmt.setString(2, dbAlias);
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public ACL getACL(String username, String dbAlias) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("select * from ACL where username = ? and dbAlias = ?");
            pstmt.setString(1, username);
            pstmt.setString(2, dbAlias);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return new ACL(rs.getString("username"), rs.getString("dbAlias"), rs.getBoolean("showDbPath"), rs.getBoolean("showDbUsername"), rs.getBoolean("showDbPassword"), rs.getBoolean("showDateTimeReg"), rs.getBoolean("editDbPath"), rs.getBoolean("editDbUsername"), rs.getBoolean("editDbPassword"));
            } else {
                return null;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return null;
        }
    }

    public ArrayList listACL() {
        ArrayList aList = new ArrayList();
        ACL acl;
        try {
            PreparedStatement pstmt = conn.prepareStatement("select dbAlias,username from ACL");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                acl = new ACL();
                acl.setDbAlias(rs.getString("dbAlias"));
                acl.setUsername(rs.getString("username"));
                aList.add(acl);
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
        }
        return aList;
    }

    public ArrayList getDBListByUser(String username) {
        ArrayList aList = new ArrayList();
        try {
            PreparedStatement pstmt = conn.prepareStatement("select dbAlias from ACL where username = ?");
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                aList.add(rs.getString("dbAlias"));
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
        }
        return aList;
    }

    public ArrayList getUserListByDB(String dbname) {
        ArrayList aList = new ArrayList();
        try {
            PreparedStatement pstmt = conn.prepareStatement("select username from ACL where dbAlias = ?");
            pstmt.setString(1, dbname);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                aList.add(rs.getString("username"));
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
        }
        return aList;
    }

    public boolean addAdmin(Admin admin) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("insert into Admin values(?,?)");
            pstmt.setString(1, admin.getUsername());
            pstmt.setString(2, admin.getDbAlias());
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public boolean updateAdmin(String oldAdmin, String newAdmin, String dbAlias) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("update Admin set username = ? where dbAlias = ? and username = ?)");
            pstmt.setString(1, newAdmin);
            pstmt.setString(2, dbAlias);
            pstmt.setString(3, oldAdmin);
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public boolean deleteAllAdmin(String dbAlias) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("delete from Admin where dbAlias = ?");
            pstmt.setString(1, dbAlias);
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public boolean deleteAdminByUserAlias(String dbAlias, String username) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("delete from Admin where dbAlias = ? and username = ?");
            pstmt.setString(1, dbAlias);
            pstmt.setString(2, username);
            int num = pstmt.executeUpdate();
            if (num == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public ArrayList getAdminByUser(String username) {
        ArrayList aList = new ArrayList();
        try {
            PreparedStatement pstmt = conn.prepareStatement("select * from Admin where username = ?");
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                aList.add(new Admin(rs.getString("username"), rs.getString("dbAlias")));
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());;
        }
        return aList;
    }

    public ArrayList getAdminByDB(String dbAlias) {
        ArrayList admList = null;
        try {
            PreparedStatement pstmt = conn.prepareStatement("select * from Admin where dbAlias = ?");
            pstmt.setString(1, dbAlias);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                admList = new ArrayList();
                while (rs.next()) {
                    admList.add(new Admin(rs.getString("username"), rs.getString("dbAlias")));
                }
            }
            return admList;
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return null;
        }
    }

    public Admin getAdminByUserDB(String username, String dbAlias) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("select * from Admin where dbAlias = ? and username =?");
            pstmt.setString(1, dbAlias);
            pstmt.setString(2, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return new Admin(rs.getString("username"), rs.getString("dbAlias"));
            } else {
                return null;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return null;
        }
    }

    public boolean checkAdminByUserDB(String username, String dbAlias) {
        try {
            PreparedStatement pstmt = conn.prepareStatement("select * from Admin where dbAlias = ? and username =?");
            pstmt.setString(1, dbAlias);
            pstmt.setString(2, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
            return false;
        }
    }

    public ArrayList listAdmin() {
        ArrayList aList = new ArrayList();
        Admin admin;
        try {
            PreparedStatement pstmt = conn.prepareStatement("select * from Admin");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                admin = new Admin(rs.getString("username"), rs.getString("dbAlias"));
                aList.add(admin);
            }
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
        }
        return aList;
    }

    public void commit() {
        try {
            conn.commit();
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
        }
    }

    public void close() {
        try {
            commit();
            conn.close();
        } catch (SQLException ex) {
            logger.log(3, ex.getMessage());
        }
    }
}
